﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace motelmanagement.common
{
    class SqlUtil : DataTable
    {
        private static OleDbConnection m_Connection;
        private OleDbCommand m_oleDbCmd;
        private OleDbDataAdapter m_DataAdapter;
        private static string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|/database/MMdata.accdb;Persist Security Info=False";
        //private static String strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MMdata.accdb;Persist Security Info=False";
        public void closeConn()
        {
            m_Connection.Close();
        }
        //dung cho update,insert,delete neu bang 1 thi ok
        public int CreateCommand(string stringSQL,OleDbCommand command)
        {
            int status = 0;
            using (OleDbConnection connection = new OleDbConnection(strConn))
            {
                // The stringSQL string contains a SQL statement that 
                // inserts,update,delete in the source table.
                command = new OleDbCommand(stringSQL);

                // Set the Connection to the new OleDbConnection.
                command.Connection = connection;

                // Open the connection and execute command. 
                try
                {
                    connection.Open();
                    status = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return status;
            }
        }
        //dung cho cau lenh select
        public void Load(OleDbCommand command)
        {
            m_DataAdapter = new OleDbDataAdapter();
            m_oleDbCmd = new OleDbCommand();
            if (m_Connection == null || m_Connection.State == ConnectionState.Closed)
            {
                m_Connection = new OleDbConnection(strConn);
                m_Connection.Open();
            }

            m_oleDbCmd = command;
            m_oleDbCmd.Connection = m_Connection;
            m_DataAdapter = new OleDbDataAdapter(m_oleDbCmd);
            this.Clear();
            m_DataAdapter.Fill(this);
        }
        public static bool OpenConnection()
        {
            try
            {
                if (m_Connection == null)
                    m_Connection = new OleDbConnection(strConn);
                if (m_Connection.State == ConnectionState.Closed)
                    m_Connection.Open();
                return true;
            }
            catch
            {
                m_Connection.Close();
                return false;
            }
        }
        public void Update()
        {
            OleDbCommandBuilder buider = new OleDbCommandBuilder(m_DataAdapter);
            m_DataAdapter.Update(this);

        }
        public int ExecuteNoneQuery()
        {
            int result = 0;
            OleDbTransaction tr = null;
            // m_Dataset = new DataSet();
            try
            {
                tr = m_Connection.BeginTransaction();

                m_oleDbCmd.Connection = m_Connection;
                m_oleDbCmd.Transaction = tr;

                m_DataAdapter = new OleDbDataAdapter();
                m_DataAdapter.SelectCommand = m_oleDbCmd;

                OleDbCommandBuilder builder = new OleDbCommandBuilder(m_DataAdapter);

                result = m_DataAdapter.Update(this);
                //m_DataAdapter.Fill(m_Dataset);
                tr.Commit();

            }
            catch
            {
                if (tr != null) tr.Rollback();

            }
            return result;
        }
        /// <summary>
        /// Thuc thi mot command
        /// </summary>
        /// <param name="command">OleDb hay Store Procedure</param>
        /// <returns></returns>
        public int ExecuteNoneQuery(OleDbCommand cmd)
        {

            int result = 0;
            OleDbTransaction tr = null;

            try
            {
                tr = m_Connection.BeginTransaction();

                cmd.Connection = m_Connection;

                cmd.Transaction = tr;

                result = cmd.ExecuteNonQuery();

                this.AcceptChanges();

                tr.Commit();

            }
            catch
            {
                if (tr != null) tr.Rollback();
                //throw;
            }
            return result;

        }

        public object ExecuteScalar(OleDbCommand cmd)
        {
            object result = null;
            OleDbTransaction tr = null;

            //try
            //{
            tr = m_Connection.BeginTransaction();

            cmd.Connection = m_Connection;

            cmd.Transaction = tr;

            result = cmd.ExecuteScalar();

            this.AcceptChanges();

            tr.Commit();

            if (result == DBNull.Value)
            {
                result = null;
            }
            //}
            //catch
            //{
            //    if (tr != null) tr.Rollback();
            //    throw;
            //}
            return result;
        }


 
    }
}
